package telsoft.app.bean;

import java.sql.*;
import java.util.*;

import telsoft.admin.util.AppAuthenticator;
import telsoft.admin.util.AppEntityProcessor;
import telsoft.app.util.*;
import telsoft.sql.*;
import telsoft.util.*;

public class AreaBean extends AppEntityProcessor
{
    public String mstrAreaID = "";
    public String mstrParentID = "";
    public String mstrAreaCode = "";
    public String mstrParentCode = "";
    public String mstrName = "";
    public String mstrFullName = "";
    public String mstrCenter = "";
    public String mstrStatus = "";
    public String mstrPrimaryKey = "";
    //private Connection mcnMain = null;
    //private String strURL = "jdbc:oracle:thin:@10.151.18.24:1521:cust";
    //private String strUser = "support";
    //private String strPass = "support123";


    protected void onInsert() throws Exception
    {
        //mcnMain = mgetConnection();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try
        {
            String strSQL = "SELECT 1 FROM area WHERE ROWNUM<=1 AND UPPER(area_id)=UPPER(?)";
            stmt = mcnMain.prepareStatement(strSQL);
            stmt.setString(1,mstrAreaID);
            rs = stmt.executeQuery();
            if(rs.next())
            {
                throw new AppException("Mã khu vực đã tồn tại","AreaBean.insert");
            }
            stmt.close();
            //////////////////////////////////////////////////////////////
            strSQL = " INSERT INTO area(area_code,parent_code,name,fullname,center_code,status,area_id)" +
                     " VALUES (?,?,?,?,?,?,?)";
            stmt = mcnMain.prepareStatement(strSQL);
            mstrAreaID = Database.getSequenceValue(mcnMain,"seq_area");
            stmt.setString(1,mstrAreaCode);
            stmt.setString(2,mstrParentCode);
            stmt.setString(3,mstrName);
            stmt.setString(4,mstrFullName);
            stmt.setString(5,mstrCenter);
            stmt.setString(6,mstrStatus);
            stmt.setString(7,mstrAreaID);
            stmt.executeUpdate();
            log.logAfterInsert(mstrLogID,"area","area_id='" + StringEscapeUtil.escapeSql(mstrPrimaryKey) + "'");
            AppAuthenticator.notifyAuthenticationChanged(mcnMain);
        }
        finally
        {
            Database.closeObject(rs);
            Database.closeObject(stmt);
						//Database.closeObject(mcnMain);
        }
    }

    ////////////////////////////////////////////////////////
    protected void onUpdate() throws Exception
    {
        //mcnMain = mgetConnection();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try
        {
            Vector vtChangeID = log.logBeforeUpdate(mstrLogID,"area","area_id='" + StringEscapeUtil.escapeSql(mstrPrimaryKey) + "'");
            String strSQL = " SELECT 1 FROM area WHERE ROWNUM<=1 AND" +
                            " UPPER(area_code)=UPPER(?) AND area_id <> ?";
            stmt = mcnMain.prepareStatement(strSQL);
            stmt.setString(1,mstrAreaCode);
            stmt.setString(2,mstrPrimaryKey);
            rs = stmt.executeQuery();
            if(rs.next())
            {
                throw new AppException("Mã khu vực đã tồn tại","AreaBean.update");
            }
            stmt.close();
            /////////////////////////////////////////////////////////
            strSQL = "UPDATE area SET area_code = ?,parent_code = ?," +
                     " name = ?,fullname = ?,center_code = ?,status = ?" +
                     " WHERE area_id = ?";
            stmt = mcnMain.prepareStatement(strSQL);
            stmt.setString(1,mstrAreaCode);
            stmt.setString(2,mstrParentCode);
            stmt.setString(3,mstrName);
            stmt.setString(4,mstrFullName);
            stmt.setString(5,mstrCenter);
            stmt.setString(6,mstrStatus);
            stmt.setString(7,mstrPrimaryKey);
            stmt.executeUpdate();
            log.logAfterUpdate(vtChangeID);
            AppAuthenticator.notifyAuthenticationChanged(mcnMain);
        }
        finally
        {
            Database.closeObject(rs);
            Database.closeObject(stmt);
            //Database.closeObject(mcnMain);
        }
    }

    ////////////////////////////////////////////////////////
    protected void onDelete() throws Exception
    {
        //mcnMain = mgetConnection();
        PreparedStatement stmt = null;
        try
        {
            log.logBeforeDelete(mstrLogID,"area","area_id='" + StringEscapeUtil.escapeSql(mstrPrimaryKey) + "'");
            String strSQL = "DELETE area WHERE area_id = ?";
            stmt = mcnMain.prepareStatement(strSQL);
            stmt.setString(1,mstrPrimaryKey);
            stmt.executeUpdate();
            AppAuthenticator.notifyAuthenticationChanged(mcnMain);
        }
        finally
        {
            Database.closeObject(stmt);
//           Database.closeObject(mcnMain);
        }
    }

    ////////////////////////////////////////////////////////
    public Vector onQuery() throws Exception
    {
        //mcnMain = mgetConnection();
        PreparedStatement stmt = null;
        Vector vtData = new Vector();
        try
        {
            String strSQL = " SELECT area_code,parent_code,name,fullname,center_code,status,area_id" +
                            " FROM area" +
 //                           " WHERE center_code = '1'" +
                            " START WITH parent_code IS NULL" +
                            " CONNECT BY PRIOR area_code = parent_code" +
                            " ORDER SIBLINGS BY status";
            stmt = mcnMain.prepareStatement(strSQL);
            vtData = Database.convertToVector(stmt.executeQuery());
        }
        finally
        {
            Database.closeObject(stmt);
            //Database.closeObject(mcnMain);
        }
        return vtData;
    }

    ////////////////////////////////////////////////////////
    public Vector QueryGroup() throws Exception
    {
        //mcnMain = mgetConnection();
        PreparedStatement stmt = null;
        String strSQL = " SELECT area_code,parent_code,name,fullname," +
                        " center_code,status,area_id" +
                        " FROM area" +
                        " ORDER BY status";
        Vector vtData = new Vector();
        try
        {
	    open();
            stmt = mcnMain.prepareStatement(strSQL);
            vtData = Database.convertToVector(stmt.executeQuery());
        }
        finally
        {
            Database.closeObject(stmt);
	    close();
            //Database.closeObject(mcnMain);
        }
        return vtData;
    }

    ////////////////////////////////////////////////////////
    public Vector QueryChild() throws Exception
    {
        //mcnMain = mgetConnection();
        PreparedStatement stmt = null;
        String strSQL = " SELECT area_id,area_code,name,fullname," +
			" center_code,case"+
		     " WHEN status='1' then 'Hiệu lực'"+
		     " else 'Hết hiệu lực'"+
		     " end"+
                        " FROM area" +
                        " WHERE parent_code = ?" +
                        " ORDER BY status";
        Vector vtData = new Vector();
        try
        {
	    open();
            stmt = mcnMain.prepareStatement(strSQL);
            stmt.setString(1,mstrParentID);
            vtData = Database.convertToVector(stmt.executeQuery());
        }
        finally
        {
            Database.closeObject(stmt);
						close();
            //Database.closeObject(mcnMain);
        }
        return vtData;
    }

    ////////////////////////////////////////////////////////
    public void loadParameter()
    {
        super.loadParameter();
        mstrAreaID = StringUtil.nvl(request.getString("mstrAreaID"),"");
        mstrParentID = StringUtil.nvl(request.getString("mstrParentID"),"");
        mstrAreaCode = StringUtil.nvl(request.getString("AreaCode"),"");
        mstrParentCode = StringUtil.nvl(request.getString("ParentCode"),"");
        mstrName = StringUtil.nvl(request.getString("Name"),"");
        mstrFullName = StringUtil.nvl(request.getString("FullName"),"");
        mstrCenter = StringUtil.nvl(request.getString("Center"),"");
        mstrStatus = StringUtil.nvl(request.getString("Status"),"");
	mstrPrimaryKey = StringUtil.nvl(request.getString("mstrPrimaryKey"),"");
    }

    ////////////////////////////////////////////////////////
    public void storeParameter()
    {
        super.storeParameter();
        response.setString("mstrAreaID",mstrAreaID);
        response.setString("mstrParentID",mstrParentID);
        response.setString("AreaCode",mstrAreaCode);
        response.setString("ParentCode",mstrParentCode);
        response.setString("Name",mstrName);
        response.setString("FullName",mstrFullName);
        response.setString("Center",mstrCenter);
        response.setString("Status",mstrStatus);
    }

    ////////////////////////////////////////////////////////
    public String getModuleName()
    {
        return telsoft.app.util.PermissionApp.FormArea;
    }

}
